<?php

declare(strict_types=1);

namespace app\command\model;

use think\facade\Db;

class DbMvcModel
{
    /**
     * 返回所有列
     * @param string $tableName
     * @param string $dbName
     * @return array COLUMN_NAME, COLUMN_TYPE, COLUMN_DEFAULT, IS_NULLABLE, CHARACTER_SET_NAME, COLLATION_NAME, `PRIVILEGES`, 
     *      EXTRA, COLUMN_COMMENT, ORDINAL_POSITION, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, COLUMN_KEY
     */
    public function getColumns($tableName, $dbName = '')
    {
        if ($dbName) {
            Db::execute('use ' . $dbName);
        }
        $field = 'COLUMN_NAME, COLUMN_TYPE, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_KEY, EXTRA, COLUMN_COMMENT, `PRIVILEGES`';
        $where = [];
        if ($dbName) {
            $where[] = ['TABLE_SCHEMA', '=', $dbName];
        }
        if ($tableName) {
            $where[] = ['TABLE_NAME', '=', $tableName];
        }
        $list = Db::table('information_schema.columns')->where($where)->order('ORDINAL_POSITION')->column($field, 'COLUMN_NAME');
        return $list;
    }

    /**
     * 返回所有数据库
     * @param string $dbName 数据库名称
     * @return array SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME
     */
    public function getDatabases($dbName = '')
    {
        $field = 'SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME';
        $where = [];
        if ($dbName) {
            $where[] = ['SCHEMA_NAME', '=', $dbName];
        } else {
            $where[] = ['SCHEMA_NAME', 'not in', ['sys', 'mysql', 'information_schema', 'performance_schema']];
        }
        $list = Db::table('information_schema.schemata')->field($field)->where($where)->order('SCHEMA_NAME')->column($field, 'SCHEMA_NAME');
        return $list;
    }
}
